﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Qu.ComputingContest.Model
{
    public class RegistrationRepository
    {
        public static Registration GetRegistration(int registrationId)
        {
            var ccDB = new ComputingContestDB();
            return ccDB.Registrations.Where(r => r.RegistrationID == registrationId).FirstOrDefault();
        }
        public static List<School> GetSchools()
        {
            var ccDB = new ComputingContestDB();
            return ccDB.Schools.OrderBy(s => s.Name).ToList();
        }

        public static bool IsRegistrationExists(Registration registration)
        {
            var ccDB = new ComputingContestDB();
            int currentContestYear = GetCurrentContestYear();

            //First check if the Mentor Already exists then use it instead of creating a new one
            foreach (var student in registration.Students)
	        {
                var studentCount =
                    (from s in ccDB.Students 
                        join r in ccDB.Registrations on s.RegistrationID equals r.RegistrationID
                     where r.Year == currentContestYear
                            && s.Email == student.Email
                        select s.StudentID).Count();
                if (studentCount > 0)
                    return true;
            }
            return false;
        }

        public static string GetRegistrationClosedMessage()
        {
            var ccDB = new ComputingContestDB();
            return ccDB.Database.SqlQuery<string>("Select dbo.GetRegistrationClosedMessage()").FirstOrDefault();
        }

        public static string GetMaxKitsReachedMessage()
        {
            var ccDB = new ComputingContestDB();
            return ccDB.Database.SqlQuery<string>("Select dbo.GetMaxKitsReachedMessage()").FirstOrDefault();
        }

        public static int GetCurrentContestYear()
        {
            var ccDB = new ComputingContestDB();
            return ccDB.Database.SqlQuery<int>("select dbo.GetConfigValueAsInt('ContestYear')").FirstOrDefault();
        }

        public static DateTime GetConfigValueAsDate(string keyName)
        {
            var ccDB = new ComputingContestDB();
            return ccDB.Database.SqlQuery<DateTime>(string.Format("select dbo.GetConfigValueAsDate('{0}')", keyName)).FirstOrDefault();
        }

        public static string GetConfigValue(string keyName)
        {
            var ccDB = new ComputingContestDB();
            return ccDB.Database.SqlQuery<string>(string.Format("select dbo.GetConfigValue('{0}')", keyName)).FirstOrDefault();
        }

        public static List<SchoolInfo> GetRegisteredSchools()
        {
            var ccDB = new ComputingContestDB();
            return ccDB.Database.SqlQuery<SchoolInfo>("GetRegisteredSchools").ToList();
        }

        public static string GetContestResourcesMessage(Registration registration)
        {
            StringBuilder msg = new StringBuilder(string.Format("Dear {0},\n", registration.Mentor.FirstName));

            msg.Append("Thank you for attending the Robots programming workshop at Qatar University. We hope that you have enjoyed it and found it beneficial.\n");
            msg.Append("We have posted some workshop resources at the contest website http://cse.qu.edu.qa/computingcontest/resources.\n\n");

            msg.Append("We hope that you find the workshop and the provided resources helpful for your team to prepare for the Contest Tasks.\n\n");

            msg.Append("Looking forward to see you on the Contest Day.\n\n");

            msg.Append("Wish you the best of luck.\n\n");
            msg.Append("Qatar University Computing Contest Team\nComputingContest@qu.edu.qa");

            return msg.ToString();
            //StringBuilder msg = new StringBuilder(string.Format("Dear {0},\n", registration.Mentor.FirstName));
            //msg.Append(string.Format("This email confirms that you are registered to participate in Qatar University Computing Contest {0}.\n", registration.Year));
            //msg.Append(string.Format("We are pleased to invite you and your registered students to one of our Robots programming training workshops on 12/3/2011 and 19/3/2011. You have selected to attend the Workshop on {0:dddd d/M/yyyy}.\n", registration.WorkshopDate));
            //msg.Append("The workshop will be from 10am to 2:30pm at Qatar University Reception Hall, Administration Building.\n\n");
            //msg.Append("Please bring your laptop so you can participate on the hands-on exercises during the workshop.\n\n");
            //msg.Append("On the day of the Workshop you will also receive a loaner Lego Kit for further practice at your school to ensure that your team is fully prepared for the Contest Tasks.\n\n");

            //msg.Append("Wish you the best of luck.\n\n");
            //msg.Append("Qatar University Computing Contest Team\nComputingContest@qu.edu.qa");

            //return msg.ToString();
        }

        public static string GetContestInvitationMessage(Registration registration)
        {

            StringBuilder msg = new StringBuilder(string.Format("Dear {0},\n", registration.Mentor.FirstName));

            msg.Append("Please find attached an invitation letter for you and the school principal to attend the contest day. The contest program is also attached.\n");
            msg.Append("Please note that the test track will be available from 9:00am to 6:00pm on Friday 29/4/2011 in the Women's Sports Facility. Please note also that 'High-heeled footwear' is not allowed in the facility. Please inform your students to wear flat shoes.\n");

            msg.Append("Best Regards\n\n");
            msg.Append("Qatar University Computing Contest Team\nhttp://cse.qu.edu.qa/computingcontest\nComputingContest@qu.edu.qa");

            return msg.ToString();
        }

        public static string GetRegistrationConfirmationMessage(Registration registration)
        {
            string maxKitsReachedMessage = GetMaxKitsReachedMessage();
            string workshopLocation = GetConfigValue("WorkshopLocation");

            StringBuilder msg = new StringBuilder(string.Format("Dear {0}, {1},\n", registration.Mentor.FirstName, registration.Mentor.School.Name));
            msg.Append(string.Format("This email confirms that you are registered to participate in Qatar University Computing Contest {0}.\n", registration.Year));
            msg.Append(string.Format("We are pleased to invite you and your registered students to our Robots programming training workshop on {0:dddd d/M/yyyy}.\n", registration.WorkshopDate));
            msg.Append(string.Format("The workshop will be from 10am to 2:30pm at {0}.\n\n", workshopLocation));

            msg.Append("Please bring your laptop so you can participate on the hands-on exercises during the workshop.\n\n");

            if (string.IsNullOrEmpty(maxKitsReachedMessage))
                msg.Append("On the day of the Workshop each registered school will receive a loaner Lego Kit for further practice to allow participating teams to prepare for the Contest Tasks.\n\n");
            else
                msg.Append(maxKitsReachedMessage).Append("\n\n");

            msg.Append("Wish you the best of luck.\n\n");
            msg.Append("Qatar University Computing Contest Team\nComputingContest@qu.edu.qa");

            return msg.ToString();
            //StringBuilder msg = new StringBuilder(string.Format("Dear {0},\n", registration.Mentor.FirstName));
            //msg.Append(string.Format("This email confirms that you are registered to participate in Qatar University Computing Contest {0}.\n", registration.Year));
            //msg.Append(string.Format("We are pleased to invite you and your registered students to one of our Robots programming training workshops on 12/3/2011 and 19/3/2011. You have selected to attend the Workshop on {0:dddd d/M/yyyy}.\n", registration.WorkshopDate));
            //msg.Append("The workshop will be from 10am to 2:30pm at Qatar University Reception Hall, Administration Building.\n\n");
            //msg.Append("Please bring your laptop so you can participate on the hands-on exercises during the workshop.\n\n");
            //msg.Append("On the day of the Workshop you will also receive a loaner Lego Kit for further practice at your school to ensure that your team is fully prepared for the Contest Tasks.\n\n");

            //msg.Append("Wish you the best of luck.\n\n");
            //msg.Append("Qatar University Computing Contest Team\nComputingContest@qu.edu.qa");

            //return msg.ToString();
        }

        public static void SendConfirmationEmail(Registration registration, string subject, string body, bool copyComputingContest, string attachmentPath=null)
        {
            string bccReceipient = string.Empty;
            if (copyComputingContest)
                bccReceipient = "ComputingContest@qu.edu.qa";

            //Send Confirmation Email
            var recipients = new List<String>();
            recipients.Add(registration.Mentor.Email);
            //recipients.Add("erradi@qu.edu.qa");

            var ccRecipients = new List<String>();
            foreach (var student in registration.Students)
                ccRecipients.Add(student.Email);
                //ccRecipients.Add("erradi@qu.edu.qa");

            MailService.SendMail(recipients, ccRecipients, bccReceipient, subject, body, attachmentPath);
        }

        public static void SendResourcesEmail(DateTime workshopDate)
        {
            var ccDB = new ComputingContestDB();
            var registrations = ccDB.Registrations.Where(r => r.WorkshopDate == workshopDate).ToList();

            foreach (var registration in registrations)
            {
                string body = GetContestResourcesMessage(registration);
                string subject = string.Format("Resources for Qatar University Computing Contest {0}", registration.Year);
                SendConfirmationEmail(registration, subject, body, false);
            }
        }

        public static void SendResourcesEmail()
        {
            var ccDB = new ComputingContestDB();
            var registrations = ccDB.Registrations.ToList();

            foreach (var registration in registrations)
            {
                string body = GetContestResourcesMessage(registration);
                string subject = string.Format("Resources for Qatar University Computing Contest {0}", registration.Year);
                SendConfirmationEmail(registration, subject, body, false);
            }
        }

        public static void SendWorkshopConfirmation(DateTime workshopDate)
        {
            var ccDB = new ComputingContestDB();
            var registrations = ccDB.Registrations.Where(r => r.WorkshopDate == workshopDate).ToList();

            foreach (var registration in registrations)
            {
                string body = GetRegistrationConfirmationMessage(registration);
                string subject = string.Format("Workshop Confirmation for Qatar University Computing Contest {0}", registration.Year);
                SendConfirmationEmail(registration, subject, body, false);
            }
        }


        public static void SendInvitationLetter (string attachmentPath)
        {
            var ccDB = new ComputingContestDB();
            var registrations = ccDB.Registrations.ToList();

            foreach (var registration in registrations)
            {
                if (registration.RegistrationID == 65)
                    continue;

                string body = GetContestInvitationMessage(registration);
                string subject = string.Format("Invitation Letter and Program for Qatar University Computing Contest {0}", registration.Year);
                SendConfirmationEmail(registration, subject, body, false, attachmentPath);
            }
        }


        public static void AddRegistration(Registration registration)
        {
            int contestYear = GetCurrentContestYear();
            var ccDB = new ComputingContestDB();

            //Save the school details
            School school = ccDB.Schools.FirstOrDefault(s => s.SchoolID == registration.Mentor.SchoolID);

            if (school != null)
            {
                if (!string.IsNullOrEmpty(registration.Mentor.School.Phone1))
                    school.Phone1 = registration.Mentor.School.Phone1;

                if (!string.IsNullOrEmpty(registration.Mentor.School.Phone2))
                    school.Phone2 = registration.Mentor.School.Phone2;

                if (!string.IsNullOrEmpty(registration.Mentor.School.Fax))
                    school.Fax = registration.Mentor.School.Fax;

                if (!string.IsNullOrEmpty(registration.Mentor.School.Email))
                    school.Email = registration.Mentor.School.Email;

                if (!string.IsNullOrEmpty(registration.Mentor.School.Website))
                    school.Website = registration.Mentor.School.Website;
            }

            //Set the School to null avoid creating a new school in the Database
            registration.Mentor.School = null;

            //First check if the Mentor Already exists then use it instead of creating a new one
            var query =
                from m in ccDB.Mentors
                where m.SchoolID == registration.Mentor.SchoolID
                    && (m.Email == registration.Mentor.Email ||
                            m.Phone == registration.Mentor.Phone ||
                            (m.FirstName == registration.Mentor.FirstName &&
                                m.LastName == registration.Mentor.LastName))
            select m;

            var mentor = query.FirstOrDefault();

            if (mentor != null)
            {
                mentor.FirstName = registration.Mentor.FirstName;
                mentor.MiddleName = registration.Mentor.MiddleName;
                mentor.LastName = registration.Mentor.LastName;
                mentor.Phone = registration.Mentor.Phone;
                mentor.Email = registration.Mentor.Email;
                mentor.Position = registration.Mentor.Position;

                registration.MentorID = mentor.MentorID;
                registration.Mentor = null;
            }

            registration.Year = contestYear;
            registration.RegistrationDate = DateTime.Now;
            
            ccDB.Registrations.Add(registration);

            ccDB.SaveChanges();

            string body = GetRegistrationConfirmationMessage(registration);
            string subject = string.Format("Registration Confirmation for Qatar University Computing Contest {0}", registration.Year);

            try
            {
                SendConfirmationEmail(registration, subject, body, true);
            }
            catch { }

        }

    }
}
